package com.jsx.hyyscript.common.util;

import com.jsx.hyyscript.common.exception.ParameterException;
import com.jsx.hyyscript.common.finalfiled.FinalStr;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;

import java.io.*;
import java.nio.charset.StandardCharsets;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author jxygzzy
 */
public class ParseExcel {
    private final String filePath;
    private final int column;

    public ParseExcel(String filePath, int column) {
        this.filePath = filePath;
        this.column = column;
    }

    public List<String[]> parseExcel() {
        return getExcelData(new File(filePath));
    }

    private List<String[]> getExcelData(File file) {
        //获得Workbook工作薄对象
        Workbook workbook = getWorkBook(file);
        //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
        List<String[]> list = new ArrayList<>();
        if (workbook != null) {
            for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
                //获得当前sheet工作表
                Sheet sheet = workbook.getSheetAt(sheetNum);
                if (sheet == null) {
                    continue;
                }
                //获得当前sheet的开始行
                int firstRowNum = sheet.getFirstRowNum();
                //获得当前sheet的结束行
                int lastRowNum = sheet.getLastRowNum();
                //循环除了所有行,如果要循环除第一行以外的就firstRowNum+1
                for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
                    //获得当前行
                    Row row = sheet.getRow(rowNum);
                    if (row == null) {
                        continue;
                    }

                    String[] cells = new String[row.getLastCellNum()];
                    //循环当前行
                    if (cells.length != column) {
                        throw new ParameterException("数据错误: 第 " + rowNum + " 行,数据不完整,每行应该有" + column + "个数据");
                    }


                    int judgeData = judgeData(row, cells);
                    switch (judgeData) {
                        case 1:
                            list.add(cells);
                            break;
                        case 2:
                            throw new ParameterException("数据错误: 第 " + (rowNum + 1) + " 行,数据不完整,每行应该有" + column + "个数据");
                        default:
                    }
                }
            }
        }
        return list;
    }

    private int judgeData(Row row, String[] cells) {
        short firstCellNum = row.getFirstCellNum();
        short lastCellNum = row.getLastCellNum();
        int numNotNull = 0;

        for (int i = firstCellNum; i < lastCellNum; i++) {
            Cell cell = row.getCell(i);
            if (StringUtils.hasText(cell.toString())) {
                cells[i] = getCellValue(cell);
                numNotNull++;
            } else {
                break;
            }
        }
        if (numNotNull == 0) {
            return 0;
        }
        if (numNotNull == cells.length) {
            return 1;
        }
        return 2;
    }


    public static Workbook getWorkBook(File file) {
        //获得文件名
        String fileName = file.getName();
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            //获取excel文件的io流
            InputStream is = new FileInputStream(file);
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if (fileName.endsWith(FinalStr.EXCEL_TYPE_1)) {
                //2003
                workbook = new HSSFWorkbook(is);
            } else if (fileName.endsWith(FinalStr.EXCEL_TYPE_2)) {
                //2007 及2007以上
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            throw new ParameterException("文件解析出错");
        }
        return workbook;
    }

    public static String getCellValue(Cell cell) {
        String cellValue = "";
        if (!StringUtils.hasText(cell.toString())) {
            return cellValue;
        }

        //判断数据的类型
        switch (cell.getCellTypeEnum()) {
            /*
             * 数字
             */
            case NUMERIC:
                cellValue = stringDateProcess(cell);
                break;
                /*
                字符串
                 */
            case STRING:
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
                /*
                Boolean
                 */
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
                /*
                公式
                 */
            case FORMULA:
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            default:
                throw new ParameterException("数据错误: 在 第 " + cell.getRowIndex() + " 行,第 " + (cell.getColumnIndex() + 1) + " 列");
        }
        return cellValue;
    }


    public static String stringDateProcess(Cell cell) {
        String result;
        // 处理日期格式、时间格式
        if (HSSFDateUtil.isCellDateFormatted(cell)) {
            SimpleDateFormat sdf;
            if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
                sdf = new SimpleDateFormat("HH:mm");
            } else {// 日期
                sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            }
            Date date = cell.getDateCellValue();
            result = sdf.format(date);
        } else if (cell.getCellStyle().getDataFormat() == 58) {
            // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            double value = cell.getNumericCellValue();
            Date date = DateUtil
                    .getJavaDate(value);
            result = sdf.format(date);
        } else {
            double value = cell.getNumericCellValue();
            CellStyle style = cell.getCellStyle();
            DecimalFormat format = new DecimalFormat();
            String temp = style.getDataFormatString();
            // 单元格设置成常规
            if ("General".equals(temp)) {
                format.applyPattern("#");
            }
            result = format.format(value);
        }

        return result;
    }

    public static void write(String text, String memo) {
        File file = new File(text);
        FileOutputStream fos = null;
        OutputStreamWriter osw = null;

        try {
            if (!file.exists()) {
                boolean hasFile = file.createNewFile();
                if (hasFile) {
                    System.out.println("file not exists, create new file");
                }
                fos = new FileOutputStream(file);
            } else {
                System.out.println("file exists");
                fos = new FileOutputStream(file, true);
            }
            osw = new OutputStreamWriter(fos, StandardCharsets.UTF_8);
            osw.write(memo + "\r\n"); //写入内容
        } catch (Exception e) {
            e.printStackTrace();
        } finally {   //关闭流
            try {
                if (osw != null) {
                    osw.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            try {
                if (fos != null) {
                    fos.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}
